Efficient Generation of Query Optimizer Diagrams

Given a parameterized n-dimensional SQL query template and a choice of query optimizer, a plan diagram is a color-coded pictorial enumeration of the execution plan choices of the optimizer over the query parameter space. Similarly, we can define cost diagram and cardinality diagram as the pictorial enumerations of cost and cardinality estimations of the optimizer over the same space. These three diagrams are collectively called “optimizer diagrams”. These diagrams have proved to be very useful for the analysis and redesign of modern optimizers but their utility is adversely impacted by the impractically large computational overheads incurred when standard bruteforce exhaustive approaches are used for producing fine-grained diagrams on high-dimensional query templates. In this report, we investigate a variety of intrusive and non-intrusive strategies for efficiently generating computationally expensive optimizer diagrams. The non-intrusive techniques use the query optimizer as a black-box and collectively feature random and grid sampling, as well as classification techniques based on nearest-neighbor and parametric query optimization. The intrusive techniques need changes in the optimizer kernel and leverage the principles of Subplan-Caching, Pilot-Passing and Plan Cost Monotonicity. We evaluate our techniques with a representative set of TPC-H-based query templates on industrial-strength optimizers. The results indicate that our non-intrusive techniques are capable of delivering 90% accurate diagrams while incurring less than 15% of the computational overheads and our intrusive techniques are able to achieve perfect diagrams with around 10% – 70% of the computational overheads when compared to the brute-force exhaustive approach. We have used the Picasso database query optimizer visualizer tool to implement our diagram production strategies and the PostgreSQL query optimizer kernel as the base of our intrusive techniques.